/*
 * Copyright (c) 2022 Huawei Technologies Co.,Ltd.
 *
 * openGauss is licensed under Mulan PSL v2.
 * You can use this software according to the terms and conditions of the Mulan PSL v2.
 * You may obtain a copy of Mulan PSL v2 at:
 *
 *          http://license.coscl.org.cn/MulanPSL2
 *
 * THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
 * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
 * MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
 * See the Mulan PSL v2 for more details.
 */

import java.sql.*;
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Properties;

/**
 * @owner: opentestcase033
 * @time: 2023/7/14
 */

public class Opengauss_SharedStorage_MultiNodeWrite_Case0057 {
    static public String driver = "org.postgresql.Driver";
    static public String tb_name;
    static public String func_name;

    public static Properties getConfigFromFile(String filePath) {
        Properties props = new Properties();
        try {
            BufferedInputStream config = new BufferedInputStream(new FileInputStream(filePath));
            props.load(config);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return props;
    }

    public static String genURLFromPro(Properties props) {
        String hostname = props.getProperty("hostname");
        String[] hostnames = hostname.split(",");
        String port = props.getProperty("port");
        String[] ports = port.split(",");
        String dbname = props.getProperty("dbname");
        tb_name = props.getProperty("tbname");
        func_name = props.getProperty("funcname");
        return genURLFromHostsPortsDBname(hostnames, ports, dbname);
    }

    public static String genURLFromHostsPortsDBname(String[] hostnames, String[] ports, String dbname) {
        String sourceURL;
        if (hostnames.length == 1) {
            sourceURL = "jdbc:postgresql://" + hostnames[0] + ":" + ports[0] + "/" + dbname;
        } else {
            ArrayList<String> ip_port_pare = new ArrayList<>();
            if (ports.length == 1) {
                for (String ip : hostnames) {
                    ip_port_pare.add(ip + ":" + ports[0]);
                }
            } else {
                for (int i = 0; i < hostnames.length; i++) {
                    ip_port_pare.add(hostnames[i] + ":" + ports[i]);
                }
            }
            sourceURL = "jdbc:postgresql://" + String.join(",", ip_port_pare) + "/" + dbname;
        }
        return sourceURL;
    }

    public static Connection GetConnection(Properties props) {
        Connection connR;
        String sourceURL = genURLFromPro(props);
        System.out.println(sourceURL);
        try {
            Class.forName(driver).newInstance();
            connR = DriverManager.getConnection(sourceURL, props);
        } catch (Exception var10) {
            var10.printStackTrace();
            return null;
        }
        return connR;
    }

    public static void CreateTableFunction(Connection conn) {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("create or replace function  " + func_name + "(tb_name varchar) return integer is " +
                    "begin " +
                    "execute immediate 'drop table if exists ' || tb_name || ' cascade;';" +
                    "execute immediate 'create table ' || tb_name || '(id int, name text);';" +
                    "return 1;" +
                    "end;");

            stmt.execute("create or replace function  " + func_name + "_dml(tb_name varchar) return integer is " +
                    "begin " +
                    "execute immediate 'insert into ' || tb_name || ' values (generate_series(1,10), ''test'');';" +
                    "execute immediate 'update ' || tb_name || ' set name=''software'' where id = 10;';" +
                    "execute immediate 'delete from ' || tb_name || ' where id=1;';" +
                    "return 1;" +
                    "end;");
            stmt.executeUpdate("drop table if exists " + tb_name + " cascade;" +
                    "create table " + tb_name + "(id int, name varchar(20));");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void ExecCallableFunctionInTransaction(Connection conn) throws SQLException, InterruptedException {
        CallableStatement cstmt = null;
        // 事务块中调用函数建表，预期报错
        conn.setAutoCommit(false);
        try {
            cstmt = conn.prepareCall("{call " + func_name + "(?)}");
            cstmt.setString(1, tb_name);
            cstmt.registerOutParameter(1, Types.INTEGER);
            cstmt.execute();
            int res = cstmt.getInt(1);
            System.out.println("The CallableStatement " + func_name + " returns:" + res);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cstmt != null) cstmt.close();
        }
        conn.commit();

        // 事务块中调用函数insert/update/delete，成功
        try {
            cstmt = conn.prepareCall("{call " + func_name + "_dml(?)}");
            cstmt.setString(1, tb_name);
            cstmt.registerOutParameter(1, Types.INTEGER);
            cstmt.execute();
            int res = cstmt.getInt(1);
            System.out.println("The CallableStatement " + func_name + "_dml returns:" + res);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cstmt != null) cstmt.close();
        }
        conn.commit();
        conn.setAutoCommit(true);
    }

    public static void QueryFunction(Connection conn) {
        try (Statement stmt = conn.createStatement()) {
            ResultSet rs = stmt.executeQuery("select t1.proname, t2.usename from pg_proc t1 inner join pg_user t2 " +
                    "on t2.usesysid=t1.proowner where proname like '" + func_name + "%';");
            while (rs.next()) {
                System.out.println(rs.getString("proname") + " | " + rs.getString("usename"));
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void QueryTable(Connection conn) {
        try (Statement stmt = conn.createStatement()) {
            ResultSet rs = stmt.executeQuery("select * from " + tb_name + ";");
            while (rs.next()) {
                System.out.println(rs.getInt("id") + " | " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void DropStatement(Connection conn) throws InterruptedException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("drop function " + func_name + ";");
            pstmt.executeUpdate();
            pstmt = conn.prepareStatement("drop function " + func_name + "_dml;");
            pstmt.executeUpdate();
            pstmt = conn.prepareStatement("drop table if exists " + tb_name + " cascade;");
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }

    public static void main(String[] args) throws InterruptedException, SQLException {
        Properties pros = new Properties();
        Properties pros_conf = null;
        String jdbc_config_file = null;
        for (int i = 0; i < args.length; i++) {
            switch (args[i]) {
                case "--config-file":
                    jdbc_config_file = args[i + 1];
                    pros_conf = getConfigFromFile(jdbc_config_file);
                    break;
                case "-F":
                    jdbc_config_file = args[i + 1];
                    pros_conf = getConfigFromFile(jdbc_config_file);
                    break;
                default:
                    break;
            }
        }
        //建立连接
        if (pros_conf != null) {
            //以参数为准，覆盖配置文件中的
            pros_conf.putAll(pros);
        }
        Connection conn = GetConnection(pros_conf);
        CreateTableFunction(conn);
        QueryFunction(conn);
        ExecCallableFunctionInTransaction(conn);
        QueryTable(conn);
        DropStatement(conn);
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
